﻿<?xml version="1.0" encoding="utf-8"?>

<dbTestFixture
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:noNamespaceSchemaLocation="file:/C:/TestFileSchema2.xsd">
  <usage>
    <![CDATA[ 
    Function introduction:
       1. Performance test for stored procedure and sql.
       2. Unit test for stored procedure and sql. 
  
    Remark:
      1. Argument size:
         For argument is variable-length data type, such as char or varchar, if its direction is Output or Return or AnonymousOutput, size attribute is mandatory. 
         if their direction is InputOutput, size attribute is also recommended to assign.
      2. exportRecordCount must be in -1, 0, other integer value greater than 0. 
             If =-1, it will output all records 
             if =0, it will not output any record
             if =Value, it will output the given number of records 
      3. For Query SQL, the name of return argument can be any string.
      4. In SQL programming perspective, there are two kinds of database whether the database supports anonymous-output argument or not.
         e.g. Oracle does not allow anonymous-output in stored procedure, because all resultsets of a stored procedure must be carried by those explicited arguments. 
         MS SQL Server and MySQL are anonymous-output supported database.
         Caution: 
           For database supporting anonymous-output, the order of each argument declaration is very important because the resultsets are retrieved according to the position of argument declaration. 
		     e.g. In MS SQL Server, if there are anonymous-output resultsets, the anonymous-output arguments must locate prior to real output arguments. 
           For database not supporting anonymous-output, the order of each argument declaration is not relevant at all. Because the resultsets are retrieved according to their argument names.
    ]]>
  </usage>
  <globalSetting>
    <dbConnection connectionName="db1"
        connectionString="Data Source=localhost\SQLEXPRESS;
          Initial Catalog=AdventureWorks;Integrated Security=True;"
        providerName="System.Data.SqlClient" />
  </globalSetting>

  <procDeclarations> 
    
    <procDeclaration alias="proc_Get_state_in_country" dbConnection="db1" 
                     name="usp_GetStatesInCountry" namespace="Person">
      <remark>This is declaration for [Person].[usp_GetStatesInCountry]</remark>
      <arguments>
        <argument name="@CountryCode" direction="Input" type="MSSQL_NCHAR"/>
        <argument name="@Return_Table" direction="AnonymousOutput" type="MSSQL_RECORDSET"/>
      </arguments>
    </procDeclaration>

    <procDeclaration alias="proc_Get_all_state" dbConnection="db1" 
                     name="usp_GetAllStates" namespace="Person">
      <remark>This is declaration for [Person].[usp_GetAllStates]</remark>
      <arguments>
        <argument name="@Return_Table" direction="AnonymousOutput" type="MSSQL_RECORDSET"/>
      </arguments>
    </procDeclaration>

    <procDeclaration alias="proc_GetStateBriefsInCountry" dbConnection="db1" 
                     name="usp_GetStateBriefsInCountry" namespace="Person">
      <remark>This is declaration for [Person].[usp_GetStateBriefsInCountry]</remark>
      <arguments>
        <argument name="@CountryCode" direction="Input" type ="MSSQL_NCHAR"/>
        <argument name="@Return_Table" direction="AnonymousOutput" type="MSSQL_RECORDSET"/>
      </arguments>
    </procDeclaration>

    <procDeclaration alias="proc_GetStateBriefsInCountry_2" dbConnection="db1" 
                     name="usp_GetStateBriefsInCountry_2" namespace="Person">
      <remark>This is declaration for [Person].[usp_GetStateBriefsInCountry_2]</remark>
      <arguments>
        <argument name="@CountryCode" direction="Input" type ="MSSQL_NCHAR"/>
        <argument name="@Return_Table" direction="AnonymousOutput" type="MSSQL_RECORDSET"/>
      </arguments>
    </procDeclaration>

    <procDeclaration alias="proc_UpdateCountryRegion" dbConnection="db1" 
                     name="usp_UpdateCountryRegion" namespace="Person">
      <remark>This is declaration for [Person].[proc_UpdateCountryRegion]</remark>
      <arguments>
        <argument name="@CountryRegionCode" direction="Input" type ="MSSQL_NCHAR"/>
        <argument name="@Name" direction="Input" type="MSSQL_NCHAR"/>
      </arguments>
    </procDeclaration>
  </procDeclarations>

  <sqlDeclarations>
    <sqlDeclaration alias="sql_get_const_number" dbConnection="db1">
      <remark>to get constant number from database server</remark>
      <sql><![CDATA[select @constant]]></sql>
      <arguments>
        <argument name="@constant" direction="Input" type="MSSQL_INT"/>
        <argument name="@result" direction="Return" type="MSSQL_INT"/>
      </arguments>
    </sqlDeclaration>

    <sqlDeclaration alias="sql_get_const_text" dbConnection="db1">
      <remark>to get constant text from database server</remark>
      <sql><![CDATA[select @constant]]></sql>
      <arguments>
        <argument name="@constant" direction="Input" type="MSSQL_INT"/>
        <argument name="@result" direction="Return" type="MSSQL_VARCHAR" size="200"/>
      </arguments>
    </sqlDeclaration>

    <sqlDeclaration alias="sql_GetCountryRegion" dbConnection="db1">
      <remark>to get country region info </remark>
      <sql><![CDATA[select * from [Person].[CountryRegion] where CountryRegionCode=@CountryCode]]></sql>
      <arguments>
        <argument name="@CountryCode" direction="Input" type="MSSQL_NCHAR"/>
        <argument name="@Return_Table" direction="Return" type="MSSQL_RECORDSET"/>
      </arguments>
    </sqlDeclaration>

    <sqlDeclaration alias ="sql_InsertRegion" dbConnection="db1">
      <remark>to insert one country region </remark>
      <sql><![CDATA[insert [Person].[CountryRegion] (CountryRegionCode, Name) values (@countryCode, @countryName)]]></sql>
      <arguments>
        <argument name ="@countryCode" type="MSSQL_VARCHAR" direction="Input"/>
        <argument name ="@countryName" type="MSSQL_VARCHAR" direction="Input"/>
      </arguments>
    </sqlDeclaration>

    <sqlDeclaration alias ="sql_DeleteRegion" dbConnection="db1">
      <remark>to delete one country region </remark>
      <sql><![CDATA[delete from [Person].[CountryRegion] where CountryRegionCode=@countryCode;]]></sql>
      <arguments>
        <argument name ="@countryCode" type="MSSQL_VARCHAR" direction="Input"/> 
      </arguments>
    </sqlDeclaration>

    <sqlDeclaration alias="sql_GetCountOfAllRegion" dbConnection="db1">
      <sql><![CDATA[select count(*) from [Person].[CountryRegion] ]]></sql>
      <arguments>
        <argument name="@returnValue" direction="Return" type="MSSQL_INT"/>
      </arguments>
    </sqlDeclaration>
    
    <sqlDeclaration alias="sql_RetrieveAllRegion" dbConnection="db1">
      <sql><![CDATA[select * from [Person].[CountryRegion] ]]></sql>
      <arguments>
        <argument name="@returnTable" direction="Return" type="MSSQL_RECORDSET"/>
      </arguments>
    </sqlDeclaration>



  </sqlDeclarations>

  <perfTestCases>
    <perfTestCase testCaseName="PT_Fetch_USA_States" statementAlias="proc_Get_state_in_country">
      <inputArguments>
        <argument name="@CountryCode" value="US"/>
      </inputArguments>
      <testCaseRemark>
        To test it take how long to execute usp_GetStatesInCountry
      </testCaseRemark>
    </perfTestCase>

    <perfTestCase testCaseName="PT_Demo_Setup" statementAlias="sql_GetCountryRegion">
      <testCaseRemark>to demo how to use setup and teardown</testCaseRemark>
      <inputArguments>
        <argument name="CountryCode" value="XX"/>
      </inputArguments>
      
      <setup>
        <statement statementAlias="sql_InsertRegion">
          <inputArguments>
            <argument name="@countryCode" value="XX"/>
            <argument name="@countryName" value="dummy country"/>
          </inputArguments>
          <remark>insert one country, its code called XX</remark>
        </statement>
      </setup>

      <teardown>
        <statement statementAlias="sql_DeleteRegion">
          <remark>to delete the country inserted in setup section</remark>
          <inputArguments>
            <argument name="@countryCode" value="XX"/>
          </inputArguments> 
        </statement>
      </teardown>
    </perfTestCase>
  </perfTestCases>

  <unitTestCases>
    <unitTestCase testCaseName="UT_TestScalarQuery" assertion="StrictEqual">
      <targetResultset statementAlias="sql_GetCountOfAllRegion" outputArgument="@returnValue">
      </targetResultset>
      <referenceResultset statementAlias="sql_get_const_number" outputArgument="result">
        <inputArguments>
          <argument name="@constant" value="238"/>
        </inputArguments>
      </referenceResultset>
    </unitTestCase>

    <unitTestCase testCaseName="UT_TestRecordsetQuery" assertion="RecordCountIsEqual">
      <targetResultset statementAlias="sql_RetrieveAllRegion" outputArgument="@returnTable">
      </targetResultset>
      <referenceResultset statementAlias="sql_get_const_number" outputArgument="result">
        <inputArguments>
          <argument name="@constant" value="238"/>
        </inputArguments>
      </referenceResultset>
    </unitTestCase>
    
    <unitTestCase testCaseName="UT_USA_states_count" assertion="RecordCountIsEqual">
      <testCaseRemark>to test how many states in USA</testCaseRemark>
      <targetResultset statementAlias="proc_Get_state_in_country" outputArgument="@return_table" >
        <inputArguments>
          <argument name="@CountryCode" value="US"/>
        </inputArguments>
      </targetResultset>
      <referenceResultset statementAlias="sql_get_const_number" outputArgument="@result">
        <inputArguments>
          <argument name="@constant" value="53"/>
        </inputArguments>
      </referenceResultset>
    </unitTestCase>

    <unitTestCase testCaseName="UT_USA_States_Is_subset_of" assertion="IsSubsetOf">
      <testCaseRemark>to judge states in USA appear in result set of [Person].[usp_GetAllStates]</testCaseRemark>
      <targetResultset statementAlias="proc_Get_state_in_country" outputArgument="return_table">
        <inputArguments>
          <argument name="@CountryCode" value="US"/>
        </inputArguments>
      </targetResultset>
      <referenceResultset statementAlias="proc_Get_all_state" outputArgument="return_table">
      </referenceResultset>
    </unitTestCase>

    <unitTestCase testCaseName="UT_Compare_Recordset_Diff_FieldCount" assertion="SetEqual">
      <testCaseRemark>
        this sample to demonstrate how to compare two recordset with different field count
      </testCaseRemark>
      <targetResultset statementAlias="proc_Get_state_in_country" outputArgument="@Return_Table" >
        <inputArguments>
          <argument name="@CountryCode" value="US"/>
        </inputArguments>
        <outputSetting>
          <field name="StateProvinceID" />
          <field name="StateProvinceCode" />
          <field name="CountryRegionCode" />
          <field name="Name" />   
        </outputSetting>
      </targetResultset>
      
      <referenceResultset statementAlias="proc_GetStateBriefsInCountry" outputArgument="@Return_Table">
        <inputArguments>
          <argument name="@CountryCode" value="US"/>
        </inputArguments>
        <outputSetting>
          <field name="StateProvinceID" />
          <field name="StateProvinceCode" />
          <field name="CountryRegionCode" />
          <field name="Name" />
        </outputSetting>
      </referenceResultset>
    </unitTestCase>

    <unitTestCase testCaseName="UT_Compare_Recordset_Diff_FieldName" assertion="SetEqual">
        <testCaseRemark>
          this sample to demonstrate how to compare two recordset with different field name
        </testCaseRemark>
        <targetResultset statementAlias="proc_GetStateBriefsInCountry_2" outputArgument="@Return_Table" >
          <inputArguments>
            <argument name="@CountryCode" value="US"/>
          </inputArguments>
          <outputSetting>
            <field name="StateProvinceID" />
            <field name="StateProvinceCode" />
            <field name="CountryRegionCode" />
            <field name="StateProvinceName"  outputName="StateProvinceName"/>
          </outputSetting>
        </targetResultset>

        <referenceResultset statementAlias="proc_GetStateBriefsInCountry" outputArgument="@Return_Table">
          <inputArguments>
            <argument name="@CountryCode" value="US"/>
          </inputArguments>
          <outputSetting>
            <field name="StateProvinceID" />
            <field name="StateProvinceCode"/>
            <field name="CountryRegionCode"/>
            <field name="Name"  outputName="StateProvinceName"/>
          </outputSetting>
        </referenceResultset>
    </unitTestCase>
    
    
 
    <unitTestCase testCaseName="UT_Test_UpdateCountryRegion" assertion="RecordCountIsEqual" testInSandbox="true">
      <setup>
        <statement statementAlias="proc_UpdateCountryRegion">
          <inputArguments>
            <argument name="@CountryRegionCode" value="ABC"/>
            <argument name="@Name" value="new country"/>
          </inputArguments>
        </statement>
      </setup>
      <targetResultset statementAlias="sql_GetCountryRegion" outputArgument="@Return_Table">
        <inputArguments>
          <argument name="@CountryCode" value="ABC"/>
        </inputArguments>
      </targetResultset>
      <referenceResultset statementAlias="sql_get_const_number" outputArgument="@result">
        <inputArguments>
          <argument name="@constant" value="1"/>
        </inputArguments>        
      </referenceResultset>
    </unitTestCase>
    
  </unitTestCases>

</dbTestFixture>